package com.icim.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.icim.entity.His_ret_prd_in;
import com.icim.entity.Ret_wo;

@Repository
public class HisRetPrdInDao {

	@Resource
	private JdbcTemplate icimHisJdbcTemplate;
	
	private His_ret_prd_in setEntityByRs(ResultSet rs) throws SQLException
	{
		His_ret_prd_in his_ret_prd_in = new His_ret_prd_in();
		his_ret_prd_in.setCus_id(rs.getString("CUS_ID"));
		his_ret_prd_in.setMtrl_prod_id_fk(rs.getString("MTRL_PROD_ID_FK"));
		his_ret_prd_in.setUnq_seq_id(rs.getString("UNQ_SEQ_ID"));
		his_ret_prd_in.setWo_id_fk(rs.getString("WO_ID_FK"));
		his_ret_prd_in.setEvt_cate(rs.getString("EVT_CATE"));
		his_ret_prd_in.setEvt_timestamp(rs.getTimestamp("EVT_TIMESTAMP"));
		his_ret_prd_in.setMtrl_box_id(rs.getString("MTRL_BOX_ID"));
		return his_ret_prd_in;
	}
	
	public List<His_ret_prd_in> findBox(List<Ret_wo> woList,Timestamp beginTimestamp,Timestamp endTimestamp)
	{
		//筛选收料和发料记录
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select * from his_ret_prd_in where evt_timestamp>=? and evt_timestamp<? ");
		sqlBuffer.append(" and evt_cate in('MTIN','MTRP') and cus_id='232'");
		
		if (woList.isEmpty()) {
			return new ArrayList<His_ret_prd_in>();
		}
		sqlBuffer.append(" and wo_id_fk in(");
		for (Ret_wo retWo : woList) {
			sqlBuffer.append("'").append(retWo.getWo_id()).append("'");
			if (woList.indexOf(retWo) < woList.size() - 1) {
				sqlBuffer.append(",");
			}
		}
		sqlBuffer.append(" )");
		return icimHisJdbcTemplate.query(sqlBuffer.toString(), new Object[] {
			beginTimestamp, endTimestamp },
			new RowMapper<His_ret_prd_in>() {

				@Override
				public His_ret_prd_in mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					return setEntityByRs(rs);
				}
				
			});
	}
	
	//根据箱子id查找，并按照时间降序排列
	public List<His_ret_prd_in> findByBoxId(String mtrl_box_id)
	{
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select * from his_ret_prd_in where");
		sqlBuffer.append(" evt_cate in('MTIN','MTRP') and cus_id='232' and mtrl_box_id='");
		sqlBuffer.append(mtrl_box_id+"' ORDER BY evt_timestamp desc");
		
		return icimHisJdbcTemplate.query(sqlBuffer.toString(), 
			new RowMapper<His_ret_prd_in>() {

				@Override
				public His_ret_prd_in mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					return setEntityByRs(rs);
				}
				
			});
	}
}
